﻿using Dapper;
using Dapper.Contrib.Extensions;
using Gx.Common.Core;
using Gx.Dal.Shared;
using Gx.Model.Entity;
using Gx.Model.Enums;
using Gx.Model.ViewModel;
using System.Collections.Generic;
using System.Linq;
namespace Gx.Dal.Manage
{
    public class LKCustomerPrePaymentDal : BaseDal<LKCustomerPrePayment>
    {

        /// <summary>
        /// 新增客户预付款
        /// </summary>
        /// <param name="lKCustomerPrePayment"></param>
        /// <returns></returns>
        public bool AddLKCustomerPrePayment(LKCustomerPrePayment lKCustomerPrePayment)
        {
            var preAmountSum = GetPreAmount(lKCustomerPrePayment.CustomerId);
            var totalAmount = preAmountSum + lKCustomerPrePayment.PreAmount;

            return UseTransaction((conn, trans) =>
            {
                var result = conn.Insert(lKCustomerPrePayment, trans);
                //获取客户预付款合计

                conn.Execute("UPDATE LK_Customer SET Balance=@totalAmount WHERE CustomerCode=@CustomerNo", new { lKCustomerPrePayment.CustomerNo, totalAmount }, trans);
                return result > 0;
            });
        }

        //更新客户预付款
        public bool UpdateLKCustomerPrePayment(LKCustomerPrePayment lKCustomerPrePayment)
        {
            var customerPrePay = Get(lKCustomerPrePayment.Id);
            var preAmountSum = GetPreAmount(lKCustomerPrePayment.CustomerId);
            var totalAmount = preAmountSum - customerPrePay.PreAmount + lKCustomerPrePayment.PreAmount;
            return UseTransaction((conn, trans) =>
            {
                var result = conn.Update(lKCustomerPrePayment, trans);

                conn.Execute("UPDATE LK_Customer SET Balance=@totalAmount WHERE CustomerCode=@CustomerNo", new { lKCustomerPrePayment.CustomerNo, totalAmount }, trans);
                return result;
            });
        }

        /// <summary>
        /// 客户预付款是否存在
        /// </summary>
        /// <param name="levelName"></param>
        /// <returns></returns>
        public bool AnyCustomerPrePay(string customerNo)
        {
            using (var conn = new DbContext())
            {
                //var total = conn.Connection.QueryFirst<int>("SELECT COUNT(1) FROM LK_CustomerLevel WHERE IsDeleted=0 AND LevelName=@levelName", new { levelName });
                //return total > 0;
                return conn.Connection.QueryFirstOrDefault<bool>("SELECT COUNT(1) FROM LK_CustomerPrePayment WHERE IsDeleted=0 AND CustomerNo=@customerNo", new { customerNo });
            }
        }

        /// <summary>
        /// 获取最大编号
        /// </summary>
        /// <returns></returns>
        public string GetMaxLKCustomerPrePaymentCode()
        {
            using (var conn = new DbContext())
            {
                return conn.Connection.QueryFirst<string>(@"SELECT MAX(CAST(SUBSTRING(BillNo, 2,LEN(BillNo)-1) AS int)) AS MaxBillNumber
FROM LK_CustomerPrePayment WITH(NOLOCK)
WHERE ISNUMERIC(SUBSTRING(BillNo, 2, LEN(BillNo) - 1)) = 1");
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public PagedList<LKCustomerPrePayment> GetLKCustomerPrePaymentPageList(LKCustomerPrePaymentInput input)
        {
            string sql = "SELECT * FROM LK_CustomerPrePayment WITH(NOLOCK) WHERE IsDeleted=0 @WHERE";
            List<string> conditions = new List<string>();
            //判断input的属性是否为空，如果不为空则添加到conditions中

            string condition = string.Empty;
            if(!string.IsNullOrWhiteSpace(input.BillNo))
            {
                input.BillNo = $"%{input.BillNo}%";
                conditions.Add("BillNo LIKE @BillNo");
            }
            if(input.CustomerId.HasValue)
            {
                conditions.Add("CustomerId=@CustomerId");
            }
            if (UserContext.RoleId != (int)Roles.管理员)
            {
                conditions.Add($"CompanyId={UserContext.CompanyId}");
            }

            if (conditions.Any())
            {
                condition = $" AND {string.Join(" AND ", conditions)}";
            }
            sql = !string.IsNullOrWhiteSpace(condition) ? sql.Replace("@WHERE", condition) : sql.Replace("@WHERE", "");

            using (var conn = new DbContext())
            {
                return conn.Connection.GetPagedList<LKCustomerPrePayment>(sql, "CreateTime Desc", input);
            }
        }


        /// <summary>
        /// 获取用户预付款余额
        /// </summary>
        /// <param name="customerId"></param>
        /// <returns></returns>
        public decimal GetPreAmount(int customerId)
        {
            using (var conn = new DbContext())
            {
                var customerPrePay = conn.Connection.Query<decimal>("SELECT SUM(PreAmount) FROM LK_CustomerPrePayment WITH(NOLOCK) WHERE IsDeleted=0 AND CustomerId=@customerId", new { customerId }).FirstOrDefault();
                var totalAmount = conn.Connection.Query<decimal>("SELECT SUM(PrePay) FROM LK_SettlementPayment WITH(NOLOCK) WHERE IsDeleted=0 AND CustomerId=@customerId", new { customerId }).FirstOrDefault();
                return customerPrePay - totalAmount;
            }
        }
    }
}

